﻿using System;
using System.Collections.Generic;
using System.Text;
using KPIS.DBM;
using System.Configuration;
using KPIS.GERP.GIMS.MODEL;
using System.Data;

namespace KPIS.GERP.GIMS.DAL
{
    public class D_IcmInventoryStockTaking
    {
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_IcmInventoryStockTaking> GetList()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_IcmInventoryStockTaking> infoList = new List<M_IcmInventoryStockTaking>();
            try
            {
                dbManager.Open();
                strSQL =   " SELECT icm_stock_taking_code,stock_taking_start_date,stock_taking_end_date,stock_taking_desc "
                          +" ,stock_taking_remark,st.job_site_seq,st.icm_wh_seq "
                          +" ,js.job_site_name,wh.icm_wh_name "
                          +" ,case when (js.job_site_name is not null) then js.job_site_name else wh.icm_wh_name end Location_name "
                          +" ,concat(coalesce(emp.first_name, ''), '  ', coalesce(emp.last_name, '')) as app_name "
                          +" ,case when (approved_when is not null and cancel_when is null) then  'ตรวจรับเรียบร้อยแล้ว' when (approved_when is null and cancel_when is not null) then 'ยกเลิกพิจารณาการตรวจนับ' "
                          +" when (approved_when is null and cancel_when is null) then 'ยังไม่ได้พิจารณา' end status_name "
                          +" FROM icm_inventory_stock_taking st  "
                          +" LEFT JOIN hrm_mp_job_sites js ON st.job_site_seq = js.job_site_seq "
                          +" LEFT JOIN icm_mas_warehouse wh ON st.icm_wh_seq = wh.icm_wh_seq "
                          +" LEFT JOIN hrm_ps_employees emp ON st.approved_by = emp.emp_seq "
                          +" LEFT JOIN hrm_mp_job_sites jsa ON  jsa.job_site_seq  = emp.working_job_site_seq "
                          +" WHERE st.record_status<>'D' "
                          +" ORDER BY icm_stock_taking_code DESC ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_IcmInventoryStockTaking info = new M_IcmInventoryStockTaking();
                        info.icm_stock_taking_code = drc[i]["icm_stock_taking_code"].ToString();
                        info.stock_taking_start_date = Convert.ToDateTime(drc[i]["stock_taking_start_date"]);
                        info.stock_taking_end_date = cn.ConvertNullDateTime(drc[i]["stock_taking_end_date"]);
                        info.stock_taking_desc = drc[i]["stock_taking_desc"].ToString();
                        info.stock_taking_remark = drc[i]["stock_taking_remark"].ToString();
                        info.job_site_seq = cn.ConvertNullInt(drc[i]["job_site_seq"]);
                        info.icm_wh_seq = cn.ConvertNullInt(drc[i]["icm_wh_seq"]);
                        info.job_site_name = drc[i]["job_site_name"].ToString();
                        info.icm_wh_name = drc[i]["icm_wh_name"].ToString();
                        info.location_name = drc[i]["Location_name"].ToString();
                        info.approved_name = drc[i]["app_name"].ToString();
                        info.status_name = drc[i]["status_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        public M_IcmInventoryStockTaking GetListFillBySeq(string icm_stock_taking_code)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_IcmInventoryStockTaking info = new M_IcmInventoryStockTaking();
            try
            {
                dbManager.Open();

                strSQL =    " SELECT icm_stock_taking_code,stock_taking_start_date,stock_taking_end_date,stock_taking_desc "
                          + " ,stock_taking_remark,st.job_site_seq,st.icm_wh_seq "
                          + " ,js.job_site_name,wh.icm_wh_name "
                          + " ,case when (js.job_site_name is not null) then js.job_site_name else wh.icm_wh_name end Location_name "
                          + " ,concat(coalesce(emp.first_name, ''), '  ', coalesce(emp.last_name, '')) as app_name "
                          + " ,case when (approved_when is not null and cancel_when is null) then  'ตรวจรับเรียบร้อยแล้ว' when (approved_when is null and cancel_when is not null) then 'ยกเลิกพิจารณาการตรวจนับ' "
                          + " when (approved_when is null and cancel_when is null) then 'ยังไม่ได้พิจารณา' end status_name "
                          + " FROM icm_inventory_stock_taking st  "
                          + " LEFT JOIN hrm_mp_job_sites js ON st.job_site_seq = js.job_site_seq "
                          + " LEFT JOIN icm_mas_warehouse wh ON st.icm_wh_seq = wh.icm_wh_seq "
                          + " LEFT JOIN hrm_ps_employees emp ON st.approved_by = emp.emp_seq "
                          + " LEFT JOIN hrm_mp_job_sites jsa ON  jsa.job_site_seq  = emp.working_job_site_seq "
                          + " WHERE st.record_status<>'D' and icm_stock_taking_code LIKE '%' '" + icm_stock_taking_code + "'  '%' ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.icm_stock_taking_code = drc[0]["icm_stock_taking_code"].ToString();
                    info.stock_taking_start_date = Convert.ToDateTime(drc[0]["stock_taking_start_date"]);
                    info.stock_taking_end_date = cn.ConvertNullDateTime(drc[0]["stock_taking_end_date"]);
                    info.stock_taking_desc = drc[0]["stock_taking_desc"].ToString();
                    info.stock_taking_remark = drc[0]["stock_taking_remark"].ToString();
                    info.job_site_seq = cn.ConvertNullInt(drc[0]["job_site_seq"]);
                    info.icm_wh_seq = cn.ConvertNullInt(drc[0]["icm_wh_seq"]);
                    info.job_site_name = drc[0]["job_site_name"].ToString();
                    info.location_name = drc[0]["Location_name"].ToString();
                    info.icm_wh_name = drc[0]["icm_wh_name"].ToString();
                    info.approved_name = drc[0]["app_name"].ToString();
                    info.status_name = drc[0]["status_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
        public M_IcmInventoryStockTaking GenerateStockTakingCode()
        {
            string strSQL;
            DataRowCollection drc = null;
            M_IcmInventoryStockTaking info = new M_IcmInventoryStockTaking();
            try
            {
                dbManager.Open();

                strSQL = " SELECT case when  (Year(Now()) <2500) then Year(Now())+543 else Year(Now()) end YearValue , Month(Now()) as MonthValue "
                         + " ,COUNT(icm_stock_taking_code)+1 as running  "
                         + " FROM icm_inventory_stock_taking "
                         + " WHERE YEAR(created_when) = YEAR(Now()) and  Month(created_when) = Month(Now()) ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.str_year = drc[0]["YearValue"].ToString();
                    info.str_month = drc[0]["MonthValue"].ToString();
                    info.stock_taking_running = drc[0]["running"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
        public void Insert_Stock_Taking(M_IcmInventoryStockTaking info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);
                dbManager.AddParameters(0, "@icm_stock_taking_code", info.icm_stock_taking_code);
                dbManager.AddParameters(1, "@stock_taking_start_date", info.stock_taking_start_date);
                dbManager.AddParameters(2, "@stock_taking_end_date", info.stock_taking_end_date);
                dbManager.AddParameters(3, "@stock_taking_desc", info.stock_taking_desc);
                dbManager.AddParameters(4, "@stock_taking_remark", info.stock_taking_remark);
                dbManager.AddParameters(5, "@icm_wh_seq", info.icm_wh_seq);
                dbManager.AddParameters(6, "@job_site_seq", info.job_site_seq);
                dbManager.AddParameters(7, "@approved_by" ,info.approved_by);
                dbManager.AddParameters(8, "@record_status", info.record_status);
                dbManager.AddParameters(9, "@created_by", info.created_by);
                dbManager.AddParameters(10, "@created_when", info.created_when);

                strSQL = " INSERT INTO icm_inventory_stock_taking "
                        + " (icm_stock_taking_code,stock_taking_start_date,stock_taking_end_date,stock_taking_desc "
                        + " ,stock_taking_remark,job_site_seq,icm_wh_seq,approved_by"
                        +" ,record_status,created_by,created_when) "
                        +" VALUES "
                        + " (@icm_stock_taking_code,@stock_taking_start_date,@stock_taking_end_date,@stock_taking_desc "
                        + " ,@stock_taking_remark,@job_site_seq,@icm_wh_seq,@approved_by"
                        + " ,@record_status,@created_by,@created_when)  ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update_Stock_Taking(M_IcmInventoryStockTaking info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);
                dbManager.AddParameters(0, "@icm_stock_taking_code", info.icm_stock_taking_code);
                dbManager.AddParameters(1, "@stock_taking_start_date", info.stock_taking_start_date);
                dbManager.AddParameters(2, "@stock_taking_end_date", info.stock_taking_end_date);
                dbManager.AddParameters(3, "@stock_taking_desc", info.stock_taking_desc);
                dbManager.AddParameters(4, "@stock_taking_remark", info.stock_taking_remark);
                dbManager.AddParameters(5, "@stock_taking_end_date", info.stock_taking_end_date);
                dbManager.AddParameters(6, "@job_site_seq", info.job_site_seq);
                dbManager.AddParameters(7, "@approved_by" ,info.approved_by);
                dbManager.AddParameters(8, "@record_status", info.record_status);
                dbManager.AddParameters(9, "@updated_by", info.updated_by);
                dbManager.AddParameters(10, "@updated_when", info.updated_when);

                strSQL = " UPDATE icm_inventory_stock_taking "
                        + " SET stock_taking_start_date = @stock_taking_start_date "
                        + " ,stock_taking_end_date = @stock_taking_end_date "
                        + " ,stock_taking_desc = @stock_taking_desc "
                        + " ,stock_taking_remark = @stock_taking_remark "
                        + " ,stock_taking_end_date = @stock_taking_end_date "
                        + " ,job_site_seq = @job_site_seq "
                        + " ,approved_by = @approved_by "
                        + " ,record_status = @record_status "
                        + " ,updated_by =@updated_by "
                        + " ,updated_when = @updated_when "
                        + " WHERE icm_stock_taking_code = @icm_stock_taking_code ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Delete_Stock_Taking(M_IcmInventoryStockTaking info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@record_status", info.record_status);
                dbManager.AddParameters(1, "@updated_by", info.updated_by);
                dbManager.AddParameters(2, "@updated_when", info.updated_when);

                strSQL = " UPDATE icm_inventory_stock_taking "
                        + " SET record_status = @record_status "
                        + " ,updated_by =@updated_by "
                        + " ,updated_when = @updated_when "
                        + " WHERE icm_stock_taking_code LIKE '%' '" + info.icm_stock_taking_code + "'  '%' ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
